﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.Geometry;
using System.Collections;
using System.Data.SqlClient;
using GDCal.vo;
using System.Data;

namespace GDCal.dao
{
    public class AreaTypeDao : AbstractDao
    {
        public static ArrayList filter(AreaTypeVO filterObj)
        {
            ArrayList ret = new ArrayList();
            SqlDataReader rs;
            AreaTypeVO temp = null;
            SqlConnection oConn = getConnection();

            String whereClause = buildWhereClause(filterObj);
            String strSQL = "SELECT id, type, description, area_group, is_default FROM Area_Type";
            if (whereClause != null)
                strSQL += whereClause;
            strSQL += " ORDER BY id ASC";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                temp = new AreaTypeVO();
                temp.setId(rs.GetInt32(0));
                temp.setType(rs.GetString(1));
                temp.setDescription(rs.GetString(2));
                if (rs.IsDBNull(3))
                    temp.setAreaGroup(0);
                else
                    temp.setAreaGroup(rs.GetInt32(3));
                if (rs.IsDBNull(4))
                    temp.setIsDefault(0);
                else
                    temp.setIsDefault(rs.GetInt32(4));
                ret.Add(temp);
            }

            oConn.Close();
            return ret;
        }

        private static String buildWhereClause(AreaTypeVO filterObj)
        {
            String ret = "";
            Boolean addAnd = false;
            if (filterObj != null)
            {
                if (filterObj.getId() > 0)
                {
                    ret += " id = " + filterObj.getId();
                    addAnd = true;
                }

                if (filterObj.getType() != null && !"".Equals(filterObj.getType()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " type like (N'%" + filterObj.getType() + "%')";
                }

                if (filterObj.getDescription() != null && !"".Equals(filterObj.getDescription()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " description like (N'%" + filterObj.getDescription() + "%')";
                }

                if (filterObj.getIsDefault() >= 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " is_default = " + filterObj.getIsDefault();
                }

                if (!"".Equals(ret))
                {
                    ret = " WHERE" + ret;
                }
            }
            return ret;
        }

        public static int delAreaType(Int32 delId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM Area_Type WHERE id = " + delId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static AreaTypeVO getAreaType(int id)
        {
            SqlDataReader rs;
            AreaTypeVO ret = null;
            if (id == 0)
            {
                ret = new AreaTypeVO();
                ret.setId(0);
                ret.setType("");
                ret.setDescription("");
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT type, description, area_group, is_default FROM Area_Type WHERE id = " + id + "";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new AreaTypeVO();
                ret.setId(id);
                ret.setType(rs.GetString(0));
                ret.setDescription(rs.GetString(1));
                if (rs.IsDBNull(2))
                    ret.setAreaGroup(0);
                else
                    ret.setAreaGroup(rs.GetInt32(2));
                if (rs.IsDBNull(3))
                    ret.setIsDefault(0);
                else
                    ret.setIsDefault(rs.GetInt32(3));
            }

            oConn.Close();
            return ret;
        }

        public static Int32 getAreaIdOfAreaGroupHasDefault(Int32 areaGroup)
        {
            Int32 ret = 0;
            SqlDataReader rs;
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT id FROM Area_Type WHERE area_group = " + areaGroup + " AND is_default = 1";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                ret = rs.GetInt32(0);
            }

            oConn.Close();
            return ret;
        }

        public static AreaTypeVO getAreaTypeByType(String type)
        {
            SqlDataReader rs;
            AreaTypeVO ret = null;
            if (String.IsNullOrEmpty(type))
            {
                ret = new AreaTypeVO();
                ret.setId(0);
                ret.setType("");
                ret.setDescription("");
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT id, description, area_group, is_default FROM Area_Type WHERE type = (N'" + type + "')";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new AreaTypeVO();
                ret.setId(rs.GetInt32(0));
                ret.setType(type);
                ret.setDescription(rs.GetString(1));
                if (rs.IsDBNull(2))
                    ret.setAreaGroup(0);
                else
                    ret.setAreaGroup(rs.GetInt32(2));
                if (rs.IsDBNull(3))
                    ret.setIsDefault(0);
                else
                    ret.setIsDefault(rs.GetInt32(3));
            }

            oConn.Close();
            return ret;
        }

        public static int updateAreaType(AreaTypeVO atVO)
        {
            int ret;
            SqlConnection oConn = getConnection();

            String strSQL = "UPDATE Area_Type SET "
                            + "type=(N'" + atVO.getType() + "'), "
                            + "description=(N'" + atVO.getDescription() + "'), "
                            + "area_group=" + atVO.getAreaGroup() + ", "
                            + "is_default=" + atVO.getIsDefault() + " "
                            + "WHERE id = " + atVO.getId();

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static int insertAreaType(AreaTypeVO stVO)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "INSERT INTO Area_Type(type, description, area_group, is_default) VALUES ("
                            + "(N'" + stVO.getType() + "'), "
                            + "(N'" + stVO.getDescription() + "'), "
                            + stVO.getAreaGroup() + ", "
                            + stVO.getIsDefault() + ")";

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }
    }
}
